﻿CREATE PROCEDURE [dbo].[proc_Project_Getlist]
	(
		@CompanyId int,
		@StartDate nvarchar(10),
		@EndDate nvarchar(10),
		@Audit int,
		@Stext nvarchar(50),
		@StartIndex int,
		@EndIndex int
	)
AS
Begin
	Declare @sRq Datetime,@eRq Datetime
	Set @sRq = Convert(Datetime,@StartDate+' 00:00:00')
	Set @eRq = Convert(Datetime,@EndDate+' 23:59:59')

	;WITH List as (Select ROW_NUMBER() OVER (ORDER BY IsFinish DESC,FinanceAudit, FinishDate ,CreateDate ,id)AS Row
		,Id
		,ProjectId
		,OrderId
		,CusName
		,ProductName
		,SupplierName
		,CostPrice
		,TypeName
		,IsFinish
		,ISNULL(FinishDate,'1900-01-01') As FinishDate
		,FinanceAudit
		,ISNULL(FinanceAuditDate,'1900-01-01') As FinanceAuditDate
		,Salesman
		,CreateDate
,NOE_Flag
		From Project
		Where (@CompanyId=0 Or CompanyId=@CompanyId) And CreateDate>=@sRq And CreateDate<=@eRq
		and (@Audit=0 Or FinanceAudit=@Audit)
		and (@Stext='' Or CusName like '%'+@Stext+'%') 
	)
	
--	Select *,(Select Count(Id) From Project
--		Where (@CompanyId=0 Or CompanyId=@CompanyId) And CreateDate>=@sRq And CreateDate<=@eRq
--		And (@Audit=0 Or FinanceAudit=@Audit)
--		And (@Stext='' Or CusName like '%'+@Stext+'%')) AS DataCount
--		,(Select Sum(ISNULL(CostPrice,0)) From Project
--		Where (@CompanyId=0 Or CompanyId=@CompanyId) And CreateDate>=@sRq And CreateDate<=@eRq
--		And (@Audit=0 Or FinanceAudit=@Audit)
--		And (@Stext='' Or CusName like '%'+@Stext+'%')) AS CostPrice_SUM
--	From list Where Row between @StartIndex and @EndIndex  
--	Order By Row

	Select *,(Select Count(1) From List) AS DataCount
		,(select sum(CostPrice) from list) AS CostPrice_SUM
	From list Where Row between @StartIndex and @EndIndex  
	Order By Row
End
